USE RealtorPortal
go

--add account
CREATE PROC sp_insertAccount
@username as varchar(50),@password as varchar(100),
@firstname as varchar(50),@lastname as varchar(50),
@email as varchar(100),@phone as varchar(15),
@company as varchar(200),@address as varchar(MAX),
@regdate as varchar(50),@paypal as varchar(200),
@roleid as int
AS
Insert into Users(Username, [Password], FirstName, LastName, Email, Phone, Company, [Address], RegDate, PaypalAccount, RoleId)
VALUES(@username, @password, @firstname, @lastname, @email,@phone, @company, @address, @regdate, @paypal, @roleid)

go

--update account

CREATE PROC sp_updateAccount
@userid as int, @password as varchar(100),
@firstname as varchar(50),@lastname as varchar(50),
@email as varchar(100),@phone as varchar(15),
@company as varchar(200),@address as varchar(MAX),
@paypal as varchar(200),@roleid as int, @isactive as bit
AS
UPDATE Users
SET [Password] = @password, FirstName = @firstname, LastName = @lastname,
Email = @email, Phone = @phone, Company = @company, [Address] = @address,
PaypalAccount = @paypal, RoleId = @roleid, IsActive = @isactive
WHERE UserId = @userid

GO
---- DELETE ACCOUNT
CREATE PROC sp_deleteAccount
@userId as int
AS 
DELETE FROM Users WHERE UserId = @userId

go

---------CHANGE PASSWORD USER
CREATE PROC sp_changePassword
@userId as int, @newpass as varchar(100)
AS
UPDATE Users set [Password] = @newpass
WHERE UserId = @userId

go
---------------------
--ADD ROLE
CREATE PROC sp_insertRole
@name as varchar(50), @description as varchar(MAX)
AS
Insert into Roles(RoleName, RoleDescription) VALUES (@name, @description)

go

---update role
CREATE PROC sp_updateRole 
@roleid as int, @name as varchar(50),  @description as varchar(MAX)
AS
UPDATE Roles SET RoleName = @name, RoleDescription = @description
WHERE RoleId = @roleid


go
--delete role
CREATE PROC sp_deleteRole
@roleid as int
AS
DELETE FROM Roles WHERE RoleId = @roleid



-------------------ADMIN
go
-- add admin account
CREATE PROC sp_insertAdmin
@user as varchar(50), @password as varchar(50), @email as varchar(100), @fullname as varchar(50)
AS
INSERT INTO Administrators(Username, [Password], Email, Fullname) VALUES(@user,@password,@email,@fullname)


go
-- edit admin
CREATE PROC sp_updateAdmin
@adminid as int, @user as varchar(50), @email as varchar(100), @fullname as varchar(50)
AS
UPDATE Administrators SET Username=@user, Email= @email, Fullname= @fullname
WHERE AdminId = @adminid

go

--delete admin
CREATE PROC sp_deleteAdmin
@adminid as int
AS
DELETE FROM Administrators WHERE AdminId = @adminid

go
-- CHANGE PASSWORD
CREATE PROC sp_changePassAdmin
@adminid as int, @newpass as varchar(50)
AS
UPDATE Administrators SET [Password] = @newpass 
WHERE AdminId = @adminid

go
-- search payment history
CREATE PROC sp_searchPaymentHistory
@from as varchar(50), @to as varchar(50)
AS
select distinct s.SubId, s.SubDate, u.Username,p.PackageName, p.Price, s.IsPaid
from Subscription as s, Packages as p, Users as u, Advertisements as a
where s.PackageId = p.PackageId and s.UserId = u.UserId and a.ExpDate > GETDATE() AND
SubDate <=Convert(Date, @to) and SubDate >= Convert(Date,@from)

go

CREATE PROC SP_SearchPaymentHistoryWithExpired
@from as varchar(50), @to as varchar(50)
AS
select distinct s.SubId, s.SubDate, u.Username,p.PackageName, p.Price, s.IsPaid
from Subscription as s, Packages as p, Users as u, Advertisements as a
where s.PackageId = p.PackageId and s.UserId = u.UserId and a.PackageId = p.PackageId and
SubDate <=Convert(Date, @to) and SubDate >= Convert(Date,@from) and a.ExpDate < GETDATE()

go
--insert comment
CREATE PROC sp_insertComment
@advId as int, @content as varchar(MAX), @date as varchar(50), @fullname as varchar(100)
AS
INSERT INTO AdsComment(AdvId, Content, [Date], FullName ) VALUES (@advId, @content, @date, @fullname)

go

-- payment
CREATE PROC sp_payAdvertisement
@advId as int
AS
BEGIN
	update Subscription
	set IsPaid ='True' where SubId in (select s.SubId
	from Advertisements as a, Packages as p, Subscription as s
	where a.PackageId = p.PackageId and p.PackageId = s.PackageId and s.SubDate = a.PostedDate and a.AdvId = @advId);
END

go 
CREATE PROC sp_DisableFeatured
@advId as int
AS
update Advertisements set IsFeatured ='False' where AdvId = @advId